package com.crealth;


import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.crealth.model.ClienteleInfoModel;


public class POITest {
	
	public static void main(String[] args) {
        Sheet sheet;
        Workbook book;
        Cell cell1, cell2;
        List<String> titlels = new ArrayList<String>();
        List<ClienteleInfoModel> readClienteleResultls = new 
        		ArrayList<ClienteleInfoModel>();
        try { 
            //t.xls为要读取的excel文件名
        	InputStream is = new FileInputStream("D:\\workspace\\loan\\LoanCRMServer\\src\\import_client_data.xlsx");
            book = WorkbookFactory.create(is);
            //获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
            sheet = book.getSheetAt(0); 
            int rowNum = sheet.getLastRowNum();
            System.out.println("rowNum: " + rowNum);
            //获取左上角的单元格
            int nCol = 0;
            Row row= sheet.getRow(0);
            while (true) {            	
            	cell1 = row.getCell(nCol);
            	if (cell1 == null) {
            		break;
            	}
            	String title = cell1.toString().trim();
            	if (title.length() == 0) {
            		break;
            	}
            	titlels.add(title);
            	nCol++;
            }
            
            int nRow = 1;
            boolean hasFinish = false;
            while(!hasFinish && nRow < sheet.getLastRowNum()) {
            	ClienteleInfoModel clientInfo = new ClienteleInfoModel();
                for (nCol = 0; nCol < titlels.size(); ++nCol ) {                	
                	String strTitle = titlels.get(nCol);
                	row= sheet.getRow(nRow);
                	cell2 = row.getCell(nCol);
                	String strValue = cell2.toString().trim();
                	if (strTitle.equals("Type")) {
                		clientInfo.setType(strValue);
                	}
                	else if (strTitle.equals("Name")) {
                		clientInfo.setName(strValue);
                	}
                	else if (strTitle.equals("Mobi")) {
                		clientInfo.setMobi(strValue);
                	}
                	else if (strTitle.equals("Tele")) {
                		clientInfo.setTele(strValue);
                	}
                	else if (strTitle.equals("Hous")) {
                		clientInfo.setHous(strValue);
                	}
                	else if (strTitle.equals("Addr")) {
                		clientInfo.setAddr(strValue);
                	}
                	else if (strTitle.equals("Unit")) {
                		clientInfo.setUnit(strValue);
                	}
                	else if (strTitle.equals("IdNo")) {
                		clientInfo.setIdNo(strValue);
                	}
                	else if (strTitle.equals("Noti")) {
                		clientInfo.setNoti(strValue);
                	}
                	else if (strTitle.equals("Dscr")) {
                		clientInfo.setDscr(strValue);
                	}
                }
                if (clientInfo.getMobi().length() == 11) {
            		readClienteleResultls.add(clientInfo);
            	}                	
            	if (clientInfo.isEmpty()) {
            		hasFinish = true;
            		break;                		
            	}                
                nRow++;
            }
            book.close();
        } catch(Exception e) {
        	System.out.println(e);
        }
	}
	
}
